# coding:utf-8
import pymysql
import pandas as pd
import re
import numpy as np
from datetime import datetime


def get_database_data():
    """从数据库获取订单数据"""
    # 打开数据库连接
    db = pymysql.connect(
                    host="localhost",
                    user="root",
                    password="hui123456",
                    db='dbtest'
    )
    cursor = db.cursor()
    # 抖音平台SQL查询
    sql_douyin = """
        SELECT  
            MONTH(day) AS "日期",
            Product_BianMa AS "商家编码", 
            SUM(Product_num) AS "商品数量" 
        FROM doudian_day 
        WHERE MONTH(day) = 9  
            AND (
                order_zt = "已签收" OR 
                order_zt = "待发货" OR 
                order_zt = "已完成" OR 
                order_zt = "已发货" OR 
                order_zt = "已支付"
            )
            AND order_sh != "退款成功"  
            AND order_sh != "售后完成"  
            AND order_sh != "退款完成"  
            AND order_sh != "已全额退款" 
        GROUP BY MONTH(day), Product_BianMa
    """

    # 其他平台SQL查询
    sql_other = """
        SELECT 
            MONTH(day) AS "日期",
            Product_BianMa AS "商家编码", 
            SUM(Product_num) AS "商品数量" 
        FROM niandu_day 
        WHERE MONTH(day) = 9  
            AND (
                order_zt = "完成" OR 
                order_zt = "等待出库" OR 
                order_zt = "等待确认收货" OR 
                order_zt = "已签收" OR 
                order_zt = "待配货" OR 
                order_zt = "待发货" OR 
                order_zt = "交易成功" OR 
                order_zt = "已完成" OR 
                order_zt = "卖家已发货，等待买家确认" OR 
                order_zt = "买家已付款，等待卖家发货" OR 
                order_zt = "已发货" OR 
                order_zt = "调度中" OR 
                order_zt = "已收货" OR 
                order_zt = "已发货未签收" OR 
                order_zt = "已发货，待签收" OR 
                order_zt = "(锁定)等待确认收货" OR 
                order_zt = "已发货，待收货" OR 
                order_zt = "部分发货" OR 
                order_zt = "买家已付款,等待卖家发货" OR 
                order_zt = "卖家已发货" OR 
                order_zt = "买家已付款" OR 
                order_zt = "发货即将超时" OR 
                order_zt = "卖家部分发货" OR 
                order_zt = "待买家收货" OR 
                order_zt = "待卖家发货" OR 
                order_zt = "部分发货中"
            )
            AND order_sh != "退款成功"  
            AND order_sh != "售后完成"  
            AND order_sh != "退款完成"  
            AND order_sh != "退货退款完成 " 
        GROUP BY MONTH(day), Product_BianMa
    """

    cursor.execute(sql_douyin)
    result_douyin = cursor.fetchall()
    cursor.execute(sql_other)
    result_other = cursor.fetchall()

    cursor.close()
    db.close()

    # 处理数据库结果
    xs1 = pd.DataFrame(list(result_douyin), columns=['日期', '商家编码', '商品数量'])
    xs11 = pd.DataFrame(list(result_other), columns=['日期', '商家编码', '商品数量'])
    date_concat_dl = pd.concat([xs1, xs11])
    date_concat_dl['商品数量'] = date_concat_dl['商品数量'].map(lambda x: int(x))

    return date_concat_dl


def process_excel_data():
    """处理Excel数据文件"""
    # 读取Excel文件
    douchao = pd.read_excel(r'G:\工作\2025年订单\9月\抖超.xlsx')
    maochao = pd.read_excel(r'G:\工作\2025年订单\9月\猫超.xlsx')
    ziying1 = pd.read_excel(r'G:\工作\2025年订单\9月\自营1.xlsx')
    ziying2 = pd.read_excel(r'G:\工作\2025年订单\9月\自营2.xlsx')
    ziying2_dl = pd.read_excel(r'G:\工作\2025年订单\9月\自营2.xlsx')
    pp1 = pd.read_excel(r'G:\工作\猫超+自营SKU编码.xlsx', dtype={'SKU': str})

    # 处理抖超数据
    douchao['退款成功金额'] = 0
    douchao['平台'] = '抖音超市'
    douchao['月'] = douchao['日期'].map(lambda x: str(x)[0:6])
    douchao_date = douchao.loc[
        :, ['月', '货品ID', '支付货品件数', '支付GMV', '退款成功金额', '支付货品件数', '平台', '货品名']]
    douchao_date.columns = ['日期', 'SKU', '销售单量', '销售额', '退款金额', '成交商品件数', '平台', '产品名称']

    # 处理猫超数据
    maochao['商品数量'] = maochao['支付商品件数'] - maochao['退款成功商品件数']
    maochao['平台'] = '猫超'
    maochao['月'] = maochao['统计日期'].map(lambda x: str(x)[0:6])
    maochao_date = maochao.loc[
        :, ['月', '商品ID', '支付子订单数(剔退款)', '支付金额(剔退款)', '退款成功金额', '商品数量', '平台']]
    maochao_date.columns = ['日期', 'SKU', '销售单量', '销售额', '退款金额', '成交商品件数', '平台']

    # 处理自营1数据
    ziying1['退款成功金额'] = 0
    ziying1['平台'] = '京东自营1'
    ziying1_date = ziying1.loc[:, ['时间', 'SKU', '成交单量', '成交金额', '退款成功金额', '成交商品件数', '平台']]
    ziying1_date.columns = ['日期', 'SKU', '销售单量', '销售额', '退款金额', '成交商品件数', '平台']

    # 处理自营2数据
    exclude_skus = [100095090135, 100111765304, 100111765306, 100122053209]
    ziying2 = ziying2[~ziying2['SKU'].isin(exclude_skus)]
    ziying2['退款成功金额'] = 0
    ziying2['平台'] = '京东自营2'
    ziying2_date = ziying2.loc[:, ['时间', 'SKU', '成交单量', '成交金额', '退款成功金额', '成交商品件数', '平台']]
    ziying2_date.columns = ['日期', 'SKU', '销售单量', '销售额', '退款金额', '成交商品件数', '平台']

    # 处理自营2_dl数据
    ziying2_dl['退款成功金额'] = 0
    ziying2_dl['平台'] = '京东自营2'
    ziying2dl_date = ziying2_dl.loc[:, ['时间', 'SKU', '成交单量', '成交金额', '退款成功金额', '成交商品件数', '平台']]
    ziying2dl_date.columns = ['日期', 'SKU', '销售单量', '销售额', '退款金额', '成交商品件数', '平台']

    # 合并数据
    res_dl = pd.concat([maochao_date, ziying1_date, ziying2dl_date])
    res_dl['SKU'] = res_dl['SKU'].astype(str).str.strip()
    res_mdl = pd.merge(res_dl, pp1, on=['SKU'], how='left')
    result_dl = pd.concat([res_mdl, douchao_date])

    return result_dl


def split_merchant_codes(result):
    """处理商家编码分列"""
    # 数据清洗
    result['商家编码'] = result['商家编码'].str.strip()
    result.dropna(subset=['商家编码'], inplace=True)
    result = result[(result['商家编码'] != 'logo定制电煮锅1个') & (result['商家编码'] != '抖音直播间赠品')]

    # 分列处理 - 使用更清晰的方式
    split_data = []
    for _, row in result.iterrows():
        merchant_code = row['商家编码']
        quantity = row['商品数量']
        date = row['日期']
        original_code = row['商家编码']

        # 分割逻辑
        if '+' in merchant_code:
            codes = merchant_code.split('+')
        elif '，' in merchant_code:
            codes = merchant_code.split('，')
            if '赠' in codes[0]:
                codes = codes[0].split('赠')
            else:
                codes = [codes[0]]
        elif '赠' in merchant_code:
            codes = merchant_code.split('赠')
        elif '＋' in merchant_code:
            codes = merchant_code.split('＋')
        else:
            codes = [merchant_code]

        # 为每个分割后的编码创建记录
        for code in codes:
            split_data.append({
                '日期': date,
                'SKU': original_code,
                '商家编码': code,
                '商品数量': quantity
            })

    return pd.DataFrame(split_data)


def extract_product_quantity(text):
    """提取商品袋数/件数"""
    if '袋' in text:
        return re.findall(r"(\d+)袋", text)[-1]
    elif '桶' in text:
        return re.findall(r"(\d+)桶", text)[-1]
    elif '个' in text:
        try:
            return re.findall(r"(\d+)个", text)[-1]
        except:
            return 1
    elif '包' in text:
        return re.findall(r"(\d+)包", text)[-1]
    elif '只' in text:
        return re.findall(r"(\d+)只", text)[-1]
    elif '根' in text:
        return re.findall(r"(\d+)根", text)[-1]
    else:
        return 0


def main():
    """主函数"""
    # 获取数据库数据
    date_concat_dl = get_database_data()

    # 处理Excel数据
    result_dl = process_excel_data()

    # 处理Excel数据格式
    pp = pd.read_excel(r'G:\工作\商家编码匹配产品.xlsx')
    result_dl = result_dl[result_dl['成交商品件数'] > 0]
    mc_date = result_dl.loc[:, ['日期', '产品名称', '成交商品件数']]
    mc_date.columns = ['日期', '商家编码', '商品数量']

    # 合并所有数据
    result = pd.concat([date_concat_dl, mc_date])

    # 处理商家编码分列
    res_d = split_merchant_codes(result)

    # 数据聚合
    result_hz = res_d.groupby([res_d['日期'], res_d['SKU'], res_d['商家编码']], as_index=False).sum()
    result_re = pd.merge(result_hz, pp, on=['商家编码'], how='left')

    # 提取件数并计算总件数
    result_re['件数'] = result_re['商家编码'].map(extract_product_quantity)
    result_re['件数'] = result_re['件数'].astype(int)
    result_re['总件数'] = result_re['商品数量'] * result_re['件数']

    # 输出结果
    result_re.to_excel(r'G:\工作\2025年订单\9月\结果\SKU结果_9月.xlsx')


if __name__ == "__main__":
    main()
